﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Dados.DSMPTUDOTableAdapters;
using System.Data.SqlClient;
using System.Data;

namespace Dados
{
    public class UsuarioDAL
    {
        static T_USUARIOTableAdapter usuarioTA = new T_USUARIOTableAdapter();

        public static DSMPTUDO.T_USUARIODataTable ConsultaTodos()
        {
            try
            {
                return usuarioTA.GetData();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static DSMPTUDO.T_USUARIODataTable ConsultaPorId(string id)
        {
            try
            {
                return usuarioTA.GetUsuarioPorId(id);
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static DSMPTUDO.T_USUARIODataTable ConsultaPorEmail(string email)
        {
            try
            {
                return usuarioTA.GetUsuarioPorEmail(email);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        public static bool Insere(string id,string nome,string email, int tipo, string senha)
        {
            try
            {
                if (usuarioTA.Insert(id, nome, email, tipo, senha)==1)
                    return true;
                else return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }    
        }

        public static bool Atualiza(string id,string nome,string email, int tipo, string senha)
        {
            try
            {
                return (usuarioTA.Update(nome, email, tipo, senha, id) == 1);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        
        public static bool Remove(string id)
        {
            try
            {
                return (usuarioTA.Delete(id) == 1);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        
        public static bool Autentica(string id, string senha)
        {
            DSMPTUDO.T_USUARIODataTable UsuarioDados;
            try
            {    
                UsuarioDados = ConsultaPorId(id);
                if (UsuarioDados.Count == 0) return false;

                if ((UsuarioDados[0].tx_senha) == (senha))
                    return true;
                else return false;
            }
            catch(Exception ex)
            {
                throw new Exception(ex.Message);
            }                          
        }


         
        public static DSMPTUDO.T_USUARIODataTable BuscaPorVideo(string nome, int genero, int autor, int formato, int swf)
        {
            string video = string.Format("Select * from T_USUARIO where tx_id in (Select tx_usuario from T_VIDEO where " +
                "tx_nome like '%" + nome + "%'" +
                (genero != 0 ? " AND nr_genero=" + genero.ToString() : "") +
                (autor != 0 ? " AND nr_autor=" + autor.ToString() : "") +
                (formato != 0 ? " AND nr_formato=" + formato.ToString() : "") +
                (swf != 0 ? " AND nr_software=" + swf.ToString() : "") + ")");

            SqlConnection conn = new SqlConnection(usuarioTA.Connection.ConnectionString);
            SqlCommand cmd = new SqlCommand(video, conn);
            cmd.CommandType = CommandType.Text;

            usuarioTA.Adapter.SelectCommand = cmd;
            DSMPTUDO.T_USUARIODataTable table = new DSMPTUDO.T_USUARIODataTable();
            usuarioTA.Adapter.Fill(table);
            return table;
        }
        
        public static DSMPTUDO.T_USUARIODataTable BuscaPorImagem(string nome, int autor, int formato, int swf)
        {
            string imagem = string.Format("Select * from T_USUARIO where tx_id in (Select tx_usuario from T_IMAGEM where " +
                 "tx_nome like '%" + nome + "%'" +
                 //(genero != 0 ? "AND nr_genero=" + genero.ToString() : "") +
                 (autor != 0 ? " AND nr_autor=" + autor.ToString() : "") +
                 (formato != 0 ? " AND nr_formato=" + formato.ToString() : "") +
                 (swf != 0 ? " AND nr_software=" + swf.ToString() : "") + ")");

            SqlConnection conn = new SqlConnection(usuarioTA.Connection.ConnectionString);
            SqlCommand cmd = new SqlCommand(imagem, conn);
            cmd.CommandType = CommandType.Text;

            usuarioTA.Adapter.SelectCommand = cmd;
            DSMPTUDO.T_USUARIODataTable table = new DSMPTUDO.T_USUARIODataTable();
            usuarioTA.Adapter.Fill(table);
            return table ;
            
        }
        

        public static DSMPTUDO.T_USUARIODataTable BuscaPorAudio(string nome, int genero, int autor, int formato, int swf)
        {
            string audio = string.Format("Select * from T_USUARIO where tx_id in (Select tx_usuario from T_AUDIO where " +
                "tx_nome like '%" + nome + "%'" +
                (genero != 0 ? " AND nr_genero=" + genero.ToString() : "") +
                (autor != 0 ? " AND nr_autor=" + autor.ToString() : "") +
                (formato != 0 ? " AND nr_formato=" + formato.ToString() : "") +
                (swf != 0 ? " AND nr_software=" + swf.ToString() : "") + ")");

            SqlConnection conn = new SqlConnection(usuarioTA.Connection.ConnectionString);
            SqlCommand cmd = new SqlCommand(audio, conn);
            cmd.CommandType = CommandType.Text;

            usuarioTA.Adapter.SelectCommand = cmd;
            DSMPTUDO.T_USUARIODataTable table = new DSMPTUDO.T_USUARIODataTable();
            usuarioTA.Adapter.Fill(table);
            return table;
        }

        public static bool AlterarSenhaUsuario(string id, string senhaAtual, string novaSenha)
        {

            try
            {
                /*método ConsultaId retorna um obj desse tipo DSMPTUDO.T_USUARIODataTable, 
                 * tenho que guardar o retorno desse metodo em uma var, para que assim possa trabalhar!
                 * 
                 * */
                DSMPTUDO.T_USUARIODataTable usuarioDT = ConsultaPorId(id);

                //comparando senha do usuario com a passada no parametro
                if (usuarioDT[0].tx_senha == senhaAtual)
                {
                    Atualiza(id, usuarioDT[0].tx_nome, usuarioDT[0].tx_email, usuarioDT[0].nr_tipo, novaSenha);
                    return true;
                }
                else
                {
                    return false;

                }

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        }
    
    }        